/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Subject;
import Admission.AdmissionBean;
import Course.CourseBean;
import Student.StudentBean;
import java.sql.*;
import java.util.Vector;

/**
 *
 * @author mishra
 */
public class Query {
    static Connection con=null;
    
    public static int add(SubjectBean sb) throws SQLException
    {
        int i=0, k=0;
        try
        {
            con=DBConnection.Connection.getConnection();
            PreparedStatement pre=con.prepareStatement("insert into subject values(subjectid.nextval,?,?,?,?,?,sysdate,?,?,?)");
            pre.setString(1, sb.getSubjectName());
            pre.setInt(2, sb.getCourseId());
            pre.setString(3, sb.getCourseStream());
            pre.setString(4, sb.getSubjectYear());
            pre.setInt(5, sb.getTotalMarks());
            pre.setInt(6, sb.getMinMarks());
            pre.setInt(7, sb.getWeightaege());
            pre.setInt(8, sb.getTheoryPractical());
            k=pre.executeUpdate();
            if(k!=0)
            {
                i=getMaxId();
            }
        }catch(Exception e)
        {
            System.out.println(e);
        }
        finally
        {
            con.close();
        }
        return i;
    }
    
    public static int getMaxId() throws SQLException
    {
        int i=0;
        try
        {
            con=DBConnection.Connection.getConnection();
            PreparedStatement pre=con.prepareStatement("select max(subject_id) from subject");
            ResultSet rs=pre.executeQuery();
            if(rs.next())
            {
                i=rs.getInt(1);
            }
        }
        catch(Exception e)
        {
            System.out.println(e);
        }finally{
            con.close();
        }
        return i;
    }
    public static SubjectBean viewSpecific(String cname, String val) throws SQLException
    {
        SubjectBean sb=new SubjectBean();
        CourseBean cb=new CourseBean();
        try{
            con=DBConnection.Connection.getConnection();
            PreparedStatement pre=con.prepareStatement("select * from subject left join course on subject.course_id=course.course_id where "+cname+"=?");
            pre.setString(1, val);
            ResultSet rs=pre.executeQuery();
            while(rs.next())
            {
                sb.setSubjectId(rs.getInt(1));
                sb.setSubjectName(rs.getString(2));
                sb.setCourseId(rs.getInt(3));
                sb.setCourseStream(rs.getString(4));
                sb.setSubjectYear(rs.getString(5));
                sb.setTotalMarks(rs.getInt(6));
                sb.setEntryDate(rs.getDate(7));
                sb.setMinMarks(rs.getInt(8));
                sb.setWeightaege(rs.getInt(9));
                sb.setTheoryPractical(rs.getInt(10));
                cb.setCourseId(rs.getInt(11));
                cb.setCourseName(rs.getString(12));
                cb.setCourseType(rs.getInt(13));
                cb.setCoursePeriod(rs.getInt(14));
                cb.setStream(rs.getString(15));
                cb.setEntryDate(rs.getDate(16));
                sb.setCourseBean(cb);
            }
        
        }
        catch(Exception e){
            System.out.println(e);
        }finally{
        con.close();}
        return sb;
    }
    public static SubjectBean viewSpecific(int val) throws SQLException
    {
        SubjectBean sb=new SubjectBean();
        CourseBean cb=new CourseBean();
        try{
            con=DBConnection.Connection.getConnection();
            PreparedStatement pre=con.prepareStatement("select * from subject left join course on subject.course_id=course.course_id where SUBJECT_ID=?");
            pre.setInt(1, val);
            ResultSet rs=pre.executeQuery();
            while(rs.next())
            {
                sb.setSubjectId(rs.getInt(1));
                sb.setSubjectName(rs.getString(2));
                sb.setCourseId(rs.getInt(3));
                sb.setCourseStream(rs.getString(4));
                sb.setSubjectYear(rs.getString(5));
                sb.setTotalMarks(rs.getInt(6));
                sb.setEntryDate(rs.getDate(7));
                sb.setMinMarks(rs.getInt(8));
                sb.setWeightaege(rs.getInt(9));
                sb.setTheoryPractical(rs.getInt(10));
                cb.setCourseId(rs.getInt(11));
                cb.setCourseName(rs.getString(12));
                cb.setCourseType(rs.getInt(13));
                cb.setCoursePeriod(rs.getInt(14));
                cb.setStream(rs.getString(15));
                cb.setEntryDate(rs.getDate(16));
                sb.setCourseBean(cb);
            }
        
        }
        catch(Exception e){
            System.out.println(e);
        }finally{
        con.close();}
        return sb;
    }
    
    public static Vector viewAll() throws SQLException
    {
        Vector<Object> list=new Vector<Object>();
        
        try{
            con=DBConnection.Connection.getConnection();
            PreparedStatement pre=con.prepareStatement("select * from subject left join course on subject.course_id=course.course_id");
            ResultSet rs=pre.executeQuery();
            while(rs.next())
            {
                SubjectBean sb=new SubjectBean();
                CourseBean cb=new CourseBean();
                sb.setSubjectId(rs.getInt(1));
                sb.setSubjectName(rs.getString(2));
                sb.setCourseId(rs.getInt(3));
                sb.setCourseStream(rs.getString(4));
                sb.setSubjectYear(rs.getString(5));
                sb.setTotalMarks(rs.getInt(6));
                sb.setEntryDate(rs.getDate(7));
                sb.setMinMarks(rs.getInt(8));
                sb.setWeightaege(rs.getInt(9));
                sb.setTheoryPractical(rs.getInt(10));
                cb.setCourseId(rs.getInt(11));
                cb.setCourseName(rs.getString(12));
                cb.setCourseType(rs.getInt(13));
                cb.setCoursePeriod(rs.getInt(14));
                cb.setStream(rs.getString(15));
                cb.setEntryDate(rs.getDate(16));
                sb.setCourseBean(cb);
                list.add(sb);
            }
        }
        catch(Exception e){
            System.out.println(e);
        }finally{
        con.close();}
        return list;
    }
public static int edit(SubjectBean sb) throws SQLException {
        int i = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("update subject set subject_name=?, course_id=?, course_stream=?, subject_year=?, total_marks=?, entrydate=sysdate, MIN_MARKS=?, WEIGHTAGE=?, THEORY_PRACTICAL=? where subject_id=?");
            pre.setString(1, sb.getSubjectName());
            pre.setInt(2, sb.getCourseId());
            pre.setString(3, sb.getCourseStream());
            pre.setString(4, sb.getSubjectYear());
            pre.setInt(5, sb.getTotalMarks());
            pre.setInt(6, sb.getMinMarks());
            pre.setInt(7, sb.getWeightaege());
            pre.setInt(8, sb.getTheoryPractical());
            pre.setInt(9, sb.getSubjectId());
            i = pre.executeUpdate();
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return i;
    }
    public static int delete(int id) throws SQLException
        {
            int i=0;
            try
            {
              con=DBConnection.Connection.getConnection();
              PreparedStatement pre=con.prepareStatement("delete from subject where subject_id=?");
              pre.setInt(1,id);
              i=pre.executeUpdate();
            }
            catch(Exception e)
            {
               return i; 
            }
            finally
            {
              con.close();  
            }
            return i;
        }    
    
    public static Vector getSubject(String stream, String sem) throws SQLException {
        Vector<SubjectBean> list=new Vector<SubjectBean>();
        PreparedStatement pre =null;
        try {
            con = DBConnection.Connection.getConnection();
           
             pre = con.prepareStatement("select * from subject where course_stream=? and subject_year=?");
             pre.setString(1, stream);
             pre.setString(2, sem);
            
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                SubjectBean sb=new SubjectBean();
                sb.setSubjectId(rs.getInt(1));
                sb.setSubjectName(rs.getString(2));
                sb.setCourseId(rs.getInt(3));
                sb.setCourseStream(rs.getString(4));
                sb.setSubjectYear(rs.getString(5));
                sb.setTotalMarks(rs.getInt(6));
                sb.setEntryDate(rs.getDate(7));
                sb.setMinMarks(rs.getInt(8));
                sb.setWeightaege(rs.getInt(9));
                sb.setTheoryPractical(rs.getInt(10));
                list.add(sb);
            }

        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return list;
    }
    
    public static Vector getStuList(int course, int sem) throws SQLException {
        Vector<Object> list=new Vector<Object>();
        PreparedStatement pre =null;
        try {
            con = DBConnection.Connection.getConnection();
            
             pre = con.prepareStatement("select * from admission a left join (select student_name, admission_id from student) s on a.ADMISSION_ID=s.ADMISSION_ID where course_id=? and semester=?");
             pre.setInt(1, course);
             pre.setInt(2, sem); 
             
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                Admission.AdmissionBean ab=new AdmissionBean();
                StudentBean sb=new StudentBean();
                ab.setAddmissionId(rs.getInt(1));
                ab.setStudentEmail(rs.getString(2));
                ab.setStudentMobile(rs.getString(3));
                ab.setRollNumber(rs.getString(4));
                ab.setStudentSession(rs.getString(5));
                ab.setCourseId(rs.getInt(6));
                ab.setApplicationNo(rs.getString(7));
                ab.setAdmissionDate(rs.getDate(8));
                ab.setEntryDate(rs.getDate(9));
                ab.setEntryEmail(rs.getString(10));
                ab.setSemester(rs.getInt(11));
                sb.setStudentName(rs.getString("student_name"));
                ab.setStudentBean(sb);
                list.add(ab);
            }

        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return list;
    }
    
    public static SubjectBean getMinMaxWeightage(int subid) throws SQLException {
        PreparedStatement pre =null;
        SubjectBean sb=new SubjectBean();
        try {
            con = DBConnection.Connection.getConnection();
            
             pre = con.prepareStatement("select total_marks,min_marks,weightage,theory_practical from subject where subject_id=?");
             pre.setInt(1, subid);
             
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                sb.setTotalMarks(rs.getInt("TOTAL_MARKS"));
                sb.setMinMarks(rs.getInt("MIN_MARKS"));
                sb.setWeightaege(rs.getInt("WEIGHTAGE"));
                sb.setTheoryPractical(rs.getInt("THEORY_PRACTICAL"));
            }

        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return sb;
    }
     
     public static Vector viewSpecificSearch(String subject, int courseid) throws SQLException
    {
        Vector<Object> list=new Vector<Object>();
        
        try{
            con=DBConnection.Connection.getConnection();
            PreparedStatement pre=con.prepareStatement("select * from subject left join course on subject.course_id=course.course_id where upper(subject.SUBJECT_NAME) like upper('"+subject+"%') and subject.COURSE_ID=?");
            pre.setInt(1, courseid);
            ResultSet rs=pre.executeQuery();
            while(rs.next())
            {
                SubjectBean sb=new SubjectBean();
                CourseBean cb=new CourseBean();
                sb.setSubjectId(rs.getInt(1));
                sb.setSubjectName(rs.getString(2));
                sb.setCourseId(rs.getInt(3));
                sb.setCourseStream(rs.getString(4));
                sb.setSubjectYear(rs.getString(5));
                sb.setTotalMarks(rs.getInt(6));
                sb.setEntryDate(rs.getDate(7));
                sb.setMinMarks(rs.getInt(8));
                sb.setWeightaege(rs.getInt(9));
                sb.setTheoryPractical(rs.getInt(10));
                cb.setCourseId(rs.getInt(11));
                cb.setCourseName(rs.getString(12));
                cb.setCourseType(rs.getInt(13));
                cb.setCoursePeriod(rs.getInt(14));
                cb.setStream(rs.getString(15));
                cb.setEntryDate(rs.getDate(16));
                sb.setCourseBean(cb);
                list.add(sb);
            }
        }
        catch(Exception e){
            System.out.println(e);
        }finally{
        con.close();}
        return list;
    }
}
